package com.chem.lems.repository;

import com.chem.lems.entity.User;

import jdk.internal.org.objectweb.asm.tree.analysis.SourceValue;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Collection;
import java.util.List;


public interface UserRepository extends JpaRepository<User, String>, JpaSpecificationExecutor<User> {
    // 模糊查询
    @Query(value = "SELECT * FROM elms_user t WHERE t.username like %?1%",nativeQuery = true)
    Page<User> findByNameLike(String content, PageRequest pageRequest);

    // 模糊查询 包括用户名和姓名
    @Query(value = "SELECT * FROM elms_user t WHERE t.username like %?1% UNION SELECT * FROM elms_user t WHERE t.nike_name like %?2%",nativeQuery = true)
    Page<User> findByAllNameLike(String userName, String name,PageRequest pageRequest);

    // 通过username查询
    @Query(value = "SELECT * FROM elms_user t WHERE t.username=?1",nativeQuery = true)
    User findByUserName(String userName);

    // 更新用户的角色
    @Transactional
    @Modifying
    @Query(value = "UPDATE elms_user_role SET role_id = ?1 WHERE user_id = ?2",nativeQuery = true)
    Integer updataUserRole(String roleId, String userId);

    // 为新用户分配角色
    @Transactional
    @Modifying
    @Query(value = "INSERT INTO `elms_user_role`(`user_id`, `role_id`) VALUES (?1, ?2)",nativeQuery = true)
    void saveUserRole(String userId, int roleId);

    // 根据角色查找被分配的用户ID
    @Query(value = "SELECT user_id FROM elms_user_role t WHERE  t.role_id =?1",nativeQuery = true)
    List<String> findStudentId(String roleId);

    // 根据角色ID，查询被分配的用户信息
    @Query(value = "SELECT * FROM elms_user WHERE user_id IN (SELECT user_id FROM elms_user_role t WHERE  t.role_id =?1)",nativeQuery = true)
    List<User> findStudents(String roleId);

    //批量分页查询,查询被分配的用户信息
    @Query(value = "SELECT * FROM elms_user WHERE user_id IN (SELECT user_id FROM elms_user_role t WHERE  t.role_id =?1)",nativeQuery = true)
    Page<User> findStudents(String roleId,PageRequest pageRequest);

    // 通过username查询
    @Query(value = "SELECT email FROM elms_user t WHERE t.user_id=?1",nativeQuery = true)
    String findEmailById(String userId);

    // 查询当前在线的用户
    @Query(value = "SELECT * FROM elms_user t WHERE t.login_state=1",nativeQuery = true)
    List<User> findAllOnline();

    // 查询时间范围内的新增用户
    @Query(value = "SELECT * FROM `elms_user` where create_time >=?1 and create_time <?2",nativeQuery = true)
    List<User> findAllAddUser(String firstday, String lastday);

    // 查询时间范围内登录的用户
    @Query(value = "SELECT * FROM `elms_user` where login_time >=?1 and login_time <?2",nativeQuery = true)
    List<User> findAllLoginUser(String firstday, String lastday);
}
